package com.jeesite.modules.student.utils;


import com.jeesite.modules.student.entity.Student;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 导入Excel工具包Util，用于通过Excel批量导入学生信息
 * @author 梁师豪LXH
 * @version 2021-07-17
 */
public class ImportExcelUtil {

    // 2003- 版本的excel
    private final static String EXCEL2003L = ".xls";
    // 2007+ 版本的excel
    private final static String EXCEL2007U = ".xlsx";

    // Excel表列总数，学生姓名，学生性别，手机号码，共3列
    private final static int COLUMN_NUM = 3;

    // 列索引下标，对应 学生姓名，第一列
    private final static int COLUMN_INDEX_NAME = 0;

    // 列索引下标，对应 学生性别，第二列
    private final static int COLUMN_INDEX_SEX = 1;

    // 列索引下标，对应 手机号码，第三列
    private final static int COLUMN_INDEX_NUMBER = 2;

    // Excel操作当前工作簿编号
    private final static int CURRENT_WORKBOOK_NO = 0;

    /**
     * 从导入Excel中获取学生数据List
     * @param in
     * @param fileName
     * @throws Exception
     */
    public static List<Student> get(InputStream in, String fileName) throws Exception {

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);

        List<Student> list = new ArrayList<>();
        Sheet sheet = null;
        Row row = null;

        //操作当前工作簿
        sheet = work.getSheetAt(CURRENT_WORKBOOK_NO);

        //开始读取Excel单元格数据
        for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            Student student = new Student();

            //读取完数据后break退出循环
            try {
                if (row.getCell(0) == null) {
                }
                ;
            } catch (NullPointerException e) {
                break;
            }

            //学生信息有3列，学生姓名，学生性别，手机号码
            for (int y = 0; y < COLUMN_NUM; y++) {
                //单元格格式化为String
                row.getCell(y).setCellType(CellType.STRING);
                //单元格取值封装入student实体中
                switch (y){
                    //取得学生姓名
                    case COLUMN_INDEX_NAME :
                        student.setStudentName(row.getCell(y).getStringCellValue());
                        break;
                    //取得学生性别
                    case COLUMN_INDEX_SEX :
                        student.setStudentSex(row.getCell(y).getStringCellValue());
                        break;
                    //取得手机号码
                    case COLUMN_INDEX_NUMBER :
                        student.setStudentNumber(row.getCell(y).getStringCellValue());
                        break;

                }

            }

            list.add(student);

        }

        return list;

    }


    /**
     * 获取Workbook对象
     * @param inStr
     * @param fileName
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {

        Workbook wb = null;

        String fileType = fileName.substring(fileName.lastIndexOf("."));

        if (EXCEL2003L.equals(fileType)) {
            // 2003- 版本的excel
            wb = new HSSFWorkbook(inStr);
        } else if (EXCEL2007U.equals(fileType)) {
            // 2007+ 版本的excel
            wb = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("解析的文件格式有误！");
        }

        return wb;
    }

}
